﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CMISLibrary.Entities;
using CMISLibrary;
using ObjectDanhMuc.DB;

namespace ObjectDanhMuc
{
    public class cls_NPC_USER_Controller
    {
         #region   Atributes

        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
        private NPC_USER info;

        #endregion

        #region   Constructor

        public cls_NPC_USER_Controller()
        {
            info = new NPC_USER();
        }
        public cls_NPC_USER_Controller(NPC_USER Info)
        {
            this.info = Info;
        }

        #endregion

        #region   Properties

        public NPC_USER pInfor
        {
            get { return info; }
            set { info = value; }
        }

        #endregion

        #region   Methods
        /// <summary>
        /// tung lam
        /// </summary>
        /// <returns></returns>
        public DataSet GET_DVIQLY_QUANTRI()
        {
            try
            {
                ds = new DataSet();
                var q = from p in CMIS2.DB.D_DVI_QLY
                        orderby p.MA_DVIQLY, p.MA_DVICTREN
                        select new {
                            p.MA_DVIQLY,
                            p.TEN_DVIQLY,
                            p.MA_DVICTREN,
                            p.CAP_DVI,
                            p.DIA_CHI,
                            p.ID_DIA_CHINH,
                            p.DIEN_THOAI,
                            p.DTHOAI_KDOANH,
                            p.DTHOAI_NONG,
                            p.DTHOAI_TRUC,
                            p.FAX,
                            p.EMAIL,
                            p.MA_STHUE,
                            p.DAI_DIEN,
                            p.CHUC_VU,
                            p.SO_UQUYEN,
                            p.NGAY_UQUYEN,
                            p.TEN_DVIUQ,
                            p.DCHI_DVIUQ,
                            p.CVU_UQUYEN,
                            p.TNGUOI_UQUYEN,
                            p.TEN_TINH,
                            p.WEBSITE
                        };
                dt = Utility.LINQToDataTable(q);
                ds.Tables.Add(dt);

                //var q2 = from p in CMIS2.DB.D_DVI_QLY where p.MA_DVIQLY == strMadviqly

                return ds;
            }
            catch
            {
                CMIS2.ResetDB(); return null;
            }
        }
        public DataSet GET_USER_QUANTRI(string USER_NAME, string PASS, string SMA_DVIQLY, ref string maloi)
        {
            try
            {
                ds = new DataSet();
                List<NPC_USER> user_name = new List<NPC_USER>();
                List<NPC_USER> user_pass = new List<NPC_USER>();
                user_name = (from p in CMIS2.DB.NPC_USER
                             where p.USERNAME.ToUpper() == USER_NAME.ToUpper() && p.MA_DVIQLY == SMA_DVIQLY && p.ISUSED == 1
                             select p).ToList();
                if (user_name.Count == 0)
                {
                    maloi = "Tên đăng nhập không tồn tại!";
                    return null;
                }
                user_pass = (from p in CMIS2.DB.NPC_USER
                             where p.USERNAME.ToUpper() == USER_NAME.ToUpper() && p.PASSWORD == PASS && p.MA_DVIQLY == SMA_DVIQLY && p.ISUSED == 1
                             select p).ToList();
                if (user_pass.Count == 0)
                {
                    maloi = "Tên đăng nhập sai mật khẩu!";
                    return null;
                }

                var result = (from p in user_pass
                              select new
                              {
                                  p.MA_DVIQLY,
                                  p.USERID,
                                  p.USERNAME,
                                  p.PASSWORD,
                                  p.FULLNAME,
                                  p.EMAIL,
                                  p.PHONE,
                                  p.MOBILE,
                                  p.DEPT,
                                  p.STATE,
                                  p.TYPEOFUSER,
                                  p.CREATEDDATE,
                                  p.CREATEDBY,
                                  p.ISUSED,
                                  p.EXPIREDTIME
                              }).ToList()
                         ;
                dt = Utility.LINQToDataTable(result);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                CMIS2.ResetDB(); maloi = ex.ToString(); return null;
            }
        }
        public bool Check_USERNAME(string USER_NAME, string SMA_DVIQLY)
        {
            try
            {
                CMIS2.ResetDB();
                ds = new DataSet();
                var q = from p in CMIS2.DB.NPC_USER
                        where p.USERNAME.ToUpper() == USER_NAME.ToUpper() && p.MA_DVIQLY == SMA_DVIQLY
                        select p;
                if (q.Count() > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                CMIS2.ResetDB(); return true;
            }
        }
        public DataSet GET_USERNAME(string SMA_DVIQLY, ref string maloi)
        {
            try
            {
                ds = new DataSet();
                var q = from p in CMIS2.DB.NPC_USER.ToList()
                        where p.TYPEOFUSER == 0 && p.MA_DVIQLY == SMA_DVIQLY
                        select new
                        {
                            MA_DVIQLY = p.MA_DVIQLY,
                            USERNAME = p.USERNAME,
                            PASSWORD = p.PASSWORD,
                            FULLNAME = p.FULLNAME,
                            EMAIL = p.EMAIL,
                            PHONE = p.PHONE,
                            USERID = p.USERID,
                            TRANGTHAI = p.ISUSED == 1 ? "Hiệu lực" : "Hết hiệu lực",
                        };
                dt = Utility.LINQToDataTable(q);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                CMIS2.ResetDB(); maloi = ex.ToString(); return null;
            }
        }
        public DataSet GET_USERNAME_NGUOIDUNG(string SMA_DVIQLY, string User, ref string maloi)
        {
            try
            {
                ds = new DataSet();
                var q = from p in CMIS2.DB.NPC_USER.ToList()
                        where p.USERNAME.ToUpper() == User.ToUpper() && p.MA_DVIQLY == SMA_DVIQLY
                        select new
                        {
                            p.MA_DVIQLY,
                            p.USERID,
                            p.USERNAME,
                            p.PASSWORD,
                            p.FULLNAME,
                            p.EMAIL,
                            p.PHONE,
                            p.MOBILE,
                            p.DEPT,
                            p.STATE,
                            p.TYPEOFUSER,
                            p.CREATEDDATE,
                            p.CREATEDBY,
                            p.ISUSED,
                            p.EXPIREDTIME
                        };
                dt = Utility.LINQToDataTable(q);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                CMIS2.ResetDB(); maloi = ex.ToString(); return null;
            }
        }
        public bool insert_NPC_USER(ref string maloi)
        {
            try
            {
                long _ID = CMIS2.DB.ExecuteCommand("Select SEQ_NPC_USER.NEXTVAL from dual");
                if (_ID == -1) return false;
                info.USERID = Convert.ToInt64(_ID);
                NPC_USER objNPC_USER = new NPC_USER()
                {
                    USERID = info.USERID,
                    USERNAME = info.USERNAME,
                    PASSWORD = info.PASSWORD,
                    MA_DVIQLY = info.MA_DVIQLY,
                    FULLNAME = info.FULLNAME,
                    EMAIL = info.EMAIL,
                    PHONE = info.PHONE,
                    MOBILE = info.MOBILE,
                    DEPT = info.DEPT,
                    STATE = info.STATE,
                    TYPEOFUSER = info.TYPEOFUSER,
                    CREATEDDATE = info.CREATEDDATE,
                    CREATEDBY = info.CREATEDBY,
                    ISUSED = info.ISUSED,
                };
                CMIS2.DB.NPC_USER.InsertOnSubmit(objNPC_USER);
                CMIS2.DB.SubmitChanges();
                CMIS2.ResetDB();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB(); return false;
            }
        }

        public bool update_NPC_USER(string s_dviqly, string username, string fullname, string pass, string email, string phone, short trangthai, ref string maloi)
        {
            try
            {

                var query = from ord in CMIS2.DB.NPC_USER
                            where ord.MA_DVIQLY == s_dviqly && ord.USERNAME.ToUpper() == username.ToUpper()
                            select ord;

                foreach (NPC_USER ord in query)
                {
                    ord.FULLNAME = fullname;
                    ord.PASSWORD = pass;
                    ord.EMAIL = email;
                    ord.PHONE = phone;
                    ord.ISUSED = trangthai;
                }

                // Submit the changes to the database.
                CMIS2.DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB(); return false;
            }
        }

        public bool delete_NPC_USER(string s_dviqly, string username, ref string maloi)
        {
            try
            {
                var query = from p in CMIS2.DB.NPC_USER where p.MA_DVIQLY == s_dviqly && p.USERNAME == username select p;
                if (query != null && query.Count() > 0)
                {
                    CMIS2.DB.NPC_USER.DeleteAllOnSubmit(query.ToList());
                    CMIS2.DB.SubmitChanges();
                    return true;
                }
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.Message;
                CMIS2.ResetDB(); return false;
            }
        }
        #endregion
    }
}
